﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.ProviderBase;
using System.Data.Common;
using System.ComponentModel;

namespace QuanLyHoSoBenhNhan
{
    public class CProvider
    {
        //Chuỗi kết nối
        static string connect_str = ConfigurationManager.ConnectionStrings["connect_strQLHSBN"].ConnectionString;

        //Tạo đối tượng kết nối tới CSDL
        public static SqlConnection conn;
        //Thuc hien store khong co tham so truyen vao
        public static DataSet Store_Procedure(string _query)
        {
            using (conn = new SqlConnection(connect_str))
            {
                conn.Open();
                //Tao ra bo lenh truy van
                SqlCommand cmd = new SqlCommand(_query, conn);

                //Loai truy van la procedure
                cmd.CommandType = CommandType.StoredProcedure;

                //Tao ra doi tuong de luu du lieu lay duoc tu database
                DataSet data = new DataSet();

                //Thuc thi cau truy van
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //Do du lieu vao trong dataset
                da.Fill(data);

                //Dong ket noi
                conn.Close();

                //Tra ve dataset
                return data;
            }
        }
        //Thuc hien store co tham so truyen vao
        public static DataSet Store_Procedure(string _query, params SqlParameter[] var)
        {
            using (conn = new SqlConnection(connect_str))
            {
                conn.Open();
                //Tao ra bo lenh truy van
                SqlCommand cmd = new SqlCommand(_query, conn);

                //Loai truy van la procedure
                cmd.CommandType = CommandType.StoredProcedure;

                //Gan cac tham so dau vao trong procedure
                foreach (var i in var)
                {
                    cmd.Parameters.Add(i);
                }

                //Tao ra doi tuong de luu du lieu lay duoc tu database
                DataSet data = new DataSet();

                //Thuc thi cau truy van
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //Do du lieu vao trong dataset
                da.Fill(data);

                //Dong ket noi
                conn.Close();

                //Tra ve dataset
                return data;
            }
        }
        public static object Store_Procedure_Output(string _query, params SqlParameter[] var)
        {
            using (conn = new SqlConnection(connect_str))
            {
                conn.Open();
                //Tao ra bo lenh truy van
                SqlCommand cmd = new SqlCommand(_query, conn);

                //Loai truy van la procedure
                cmd.CommandType = CommandType.StoredProcedure;

                //string temp;
                string str = "";
                //Gan cac tham so dau vao trong procedure
                foreach (var i in var)
                {
                    if (i.Direction == ParameterDirection.ReturnValue)
                    {
                        str = i.ParameterName;
                    }
                    cmd.Parameters.Add(i);
                }

                object value = null;
                //Thuc thi cau truy van

                cmd.ExecuteNonQuery();
                value = cmd.Parameters[str].Value.ToString();
                //Dong ket noi
                conn.Close();

                //Tra ve dataset
                return value;
            }
        }
        //Thuc hien cau truy van
        public static DataSet Query(string _query)
        {
            using (conn = new SqlConnection(connect_str))
            {
                //Tao ra bo lenh truy van
                SqlCommand cmd = new SqlCommand(_query, conn);

                //Tao ra doi tuong de luu du lieu lay duoc tu database
                DataSet data = new DataSet();

                //Thuc thi cau truy van
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //Do du lieu vao trong dataset
                da.Fill(data);

                //Dong ket noi
                conn.Close();

                //Tra ve dataset
                return data;
            }
        }
        //Thuc hien cau truy van
        public static DataSet Query(string _query, params SqlParameter[] var)
        {
            using (conn = new SqlConnection(connect_str))
            {
                //Tao ra bo lenh truy van
                SqlCommand cmd = new SqlCommand(_query, conn);

                //Tao ra doi tuong de luu du lieu lay duoc tu database
                DataSet data = new DataSet();
                foreach (var i in var)
                {
                    cmd.Parameters.Add(i);
                }
                //Thuc thi cau truy van
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                //Do du lieu vao trong dataset
                da.Fill(data);

                //Dong ket noi
                conn.Close();

                //Tra ve dataset
                return data;
            }
        }
        //tao parameter
        public static SqlParameter Parameter(string name, object value)
        {
            SqlParameter para = new SqlParameter(name, value);
            return para;
        }
        public static SqlParameter Parameter(string name, object value, DbType type)
        {
            SqlParameter para = new SqlParameter(name, value);
            para.DbType = type;
            return para;
        }
        public static SqlParameter Parameter(string name, object value, ParameterDirection style)
        {
            SqlParameter para = new SqlParameter(name, value);
            para.Direction = style;
            return para;
        }
    }
   
}
